
package com.feib.ebilling.job.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.feib.ebilling.job.dao.BankingFeedbackDao;


/**
 * @title (#)BankingFeedbackService.java<br>
 * @description <br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2010/12/10 create by Jimmy Liu<br>
 */

@Service("bankingFeedbackService")
public class BankingFeedbackService extends BaseService {

	public static Map<String, String> CHANEL_CODE = new HashMap<String, String>();
    static
    {
        CHANEL_CODE.put("7111111", "D");
        CHANEL_CODE.put("HILIFE", "F");
        CHANEL_CODE.put("TFM", "E");       
        CHANEL_CODE.put("OKCVS", "G");
        CHANEL_CODE.put("POST", "H");
        /* For 中租用 */
        CHANEL_CODE.put("1", "D");
        CHANEL_CODE.put("2", "F");
        CHANEL_CODE.put("3", "E");       
        CHANEL_CODE.put("4", "G");
        CHANEL_CODE.put("5", "H");
    }
	
    @Resource(name="bankingFeedbackDao")
    private BankingFeedbackDao bankingFeedbackDao;
    
    @Resource(name="txManager")
    private PlatformTransactionManager transactionManager;
    
    
    /**
     * 虛擬帳號入帳實體帳號列表
     * @return 實體帳戶與業者名稱List
     */
    @Transactional(readOnly = true)
    public List<Map<String, Object>> getRealAccounts()
    {
        return bankingFeedbackDao.queryRealAccounts();
    }

    @Transactional(readOnly = false)
    public int addBankFeedbackLog(String[] contents, String fileName)
    {
        return this.bankingFeedbackDao.insertBankFeedbackLog(contents, fileName);
    }

    @Transactional(readOnly = false)
    public int removeBankFeedbackLog(String fileName)
    {
        return this.bankingFeedbackDao.deleteBankFeedbackLog(fileName);
    }

    
    @Transactional(readOnly = false)
    public void writeoffByFile(String fileName)
    {
        // 建立交易的定義，每一次都是新的 Transaction
        DefaultTransactionDefinition definition = new DefaultTransactionDefinition(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
        
        // 取得 DB Connection 
        Connection con = DataSourceUtils.getConnection(bankingFeedbackDao.getDataSource());
        Statement stmt = null;
        try {
            stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String sql = "select * from FEEDBACK_LOG where FBFILENAME = '" + fileName + "' and FEEDBACK_TYPE = 1 AND FBCODE = '0'";
            ResultSet rs = null;
            try {
                rs = stmt.executeQuery(sql);
                while (rs.next()){

                    TransactionStatus status = this.transactionManager.getTransaction(definition);                    
                    
                    /* 每一筆都要作，就算中間有 exception */
                    try {
                        processSingleWriteoffPayment(rs);
                        transactionManager.commit(status);
                    }
                    catch (Exception e) {
                        
                        logger.error("[錯誤]"+ e.getMessage(),  e);
                        
                        /* 異常，需例外處理 */
                        transactionManager.rollback(status);
                        status = null; // roll back 不需要再呼叫 commit
                        
                        // 更新WriteOffPayment 狀態
                        long uid = rs.getLong("UID");
                        
                        TransactionStatus status2 = this.transactionManager.getTransaction(definition);
                        try {
                            String msg = e.getMessage();
                            if (100 < msg.getBytes("MS950").length)
                            {
                                msg = new String(msg.getBytes("MS950"), 0, 100, "MS950");
                            }
                            System.out.println(msg);
                            System.out.println(msg.getBytes().length);
                            System.out.println(msg.getBytes("MS950").length);
                            this.bankingFeedbackDao.updateWriteoffException(uid, msg);
                            transactionManager.commit(status2);
                        }
                        catch (Exception e2) {
                            logger.error("回寫銷帳狀態錯誤", e2);
                            transactionManager.rollback(status2);
                            status2 = null;
                        }
                    }
                } // end of while (rs.next())
                
            }
            finally {
                JdbcUtils.closeResultSet(rs);
            }
        }
        catch (Exception e) {
            JdbcUtils.closeStatement(stmt);
            stmt = null;
            DataSourceUtils.releaseConnection(con, bankingFeedbackDao.getDataSource());
            con = null;
            throw new RuntimeException(e);
        }
        finally
        {
            JdbcUtils.closeStatement(stmt);
            stmt = null;
            DataSourceUtils.releaseConnection(con, bankingFeedbackDao.getDataSource());
            con = null;
        }
    }

    /**
     * 臨櫃、自動化通路銷帳邏輯<br>
     * 單筆銷帳
     * @param rs
     * @throws SQLException
     * @throws ParseException 
     */
	private void processSingleWriteoffPayment(ResultSet rs)throws SQLException, ParseException {
		
		/* 虛擬帳號 */
		String userno = rs.getString("USERNO");		
        /* 20111014 查詢GROUP_TYPE不等於「4」.車貸策略聯盟的進行銷帳 */
		List<Map<String, Object>> groups = this.bankingFeedbackDao.queryFeedbacklogByGroupType(userno);
		if (0 == groups.size()) {
			/* 用虛擬帳號查詢繳費單資料 */
			if ("0".equals(rs.getString("EC"))) {
				doEC0(rs);
			} else {
				doEC1(rs);
			}
		}else{
			logger.info( userno + "：車貸策略聯盟帳號暫不銷帳");
		}
	}

    /**
     * 正常銷帳 
     * @param rs
     * @param payments
     * @throws SQLException
     * @throws ParseException 
     */
    private void doEC0(ResultSet rs ) throws SQLException, ParseException
    {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmsshh");
        
        long uid = rs.getLong("UID");
        String virtualAccount = rs.getString("USERNO"); //虛擬帳號       
        long txamt = Long.parseLong(rs.getString("TXAMT"));
        
        /* 正常銷帳  沖正欄位為0 */ /* 未銷帳資料 */                        
        List<Map<String, Object>> payments = this.bankingFeedbackDao.queryUnWriteOffByVirtualAccount(virtualAccount);
        
        if (0 == payments.size())
        {
            if ("2".equals(rs.getString("TRMTYP")))
            {
                /* 通路別TRMTYP ==> (0:臨櫃,1:網銀,2:ATM(或網路ATM),3:中心轉帳BIO),4:自行XML,8:跨行匯款*/
                /* 通路別為2時，且使用者已線上繳款，則此筆略過不處理*/
                int rd = this.bankingFeedbackDao.countRealTimeWriteOffByVirtualAccount(virtualAccount);
                if ( 0 == rd)
                {
                    /* 沒有線上繳款資料，需例外處理*/
                    this.bankingFeedbackDao.updateWriteoffException(uid, "無未銷帳資料");
                }
            }
            else
            {
                /* 未找到，需例外處理 */
                this.bankingFeedbackDao.updateWriteoffException(uid, "無未銷帳資料");
            }
        }
        else if (1 == payments.size())
        {
            Map<String, Object> payment = payments.get(0);
            /* 核對金額是否正確 */
            long totalAmount = ((Number)payment.get("TOTAL_AMOUNT")).longValue(); // 繳費總金額
            long totalLoanableAmount = 0;
            if (payment.get("TOTAL_LOANABLE_AMOUNT") != null){
                totalLoanableAmount = ((Number)payment.get("TOTAL_LOANABLE_AMOUNT")).longValue(); // 繳費總金額(可貸)
            }    
            String aday = rs.getString("ADAY"); //交易日/顧客繳費日, 西元年月日
            String atime = rs.getString("ATIME"); //交易日/顧客繳費日, 西元年月日
            Date fbTime = rs.getDate("FBTIME"); //銷帳時間(繳款核銷日), add by Jimmy Liu on 2011.04.07 因應報表需要 繳款交易日與繳款核銷日 修改
            
            if (txamt == totalAmount || txamt == totalLoanableAmount)
            {
                // Yes!
                Map<String, Object> writeoffPayment = createEmptyWriteoffPayment();
                
                /* 先新增到銷帳資料表*/

                writeoffPayment.put("PAYMENT_PK", payment.get("PAYMENT_PK"));
                writeoffPayment.put("AMOUNT", txamt);//銷帳金額
                
                /*通路別TRMTYP ==> (0:臨櫃,1:網銀,2:ATM(或網路ATM),3:中心轉帳BIO),4:自行XML,8:跨行匯款*/
                writeoffPayment.put("CHANNEL", rs.getString("TRMTYP"));//通路 (帳單逾期可收)
                
                writeoffPayment.put("TX_DATE", dateFormat.parseObject(aday+atime));//繳款交易日  ADAY, add by Jimmy Liu on 2011.04.07 因應報表需要 繳款交易日與繳款核銷日 修改
                writeoffPayment.put("WRITE_OFF_DATE", fbTime);//銷帳時間(繳款核銷日), modify by Jimmy Liu on 2011.04.07 因應報表需要 繳款交易日與繳款核銷日 修改                
                writeoffPayment.put("WRITE_OFF_STATUS", new Integer(1)); //銷帳狀態- 正常
                writeoffPayment.put("FEEDBACK_UID", new Long(uid));
                
                Number writeOffPaymentPk = this.bankingFeedbackDao.insertWriteOffPayment(writeoffPayment);
                
                /* 再更新 Payment */
                payment.put("REAL_PAY_AMOUNT", txamt);
                payment.put("WRITE_OFF_PAYMENT_PK", writeOffPaymentPk);
                payment.put("UPDATE_BY", "BankingFeedBackbackJob");
                payment.put("UPDATE_DATE", new Date());
                this.bankingFeedbackDao.updatePaymentToWriteOffStatus(payment);
                
                this.bankingFeedbackDao.updateWriteoffDone(uid);
                
            }
            else
            {
                /* 金額不符合，需例外處理 */
                this.bankingFeedbackDao.updateWriteoffException(uid, "金額不符合");
            }
        }
        else
        {
            /* 只能有一筆未消帳資料，需例外處理  */ /* 源頭就有錯了，需要改轉入程式，或是修改規則 */
            this.bankingFeedbackDao.updateWriteoffException(uid, "多筆未銷帳資料符合");
        }
        
    }

    /**
     * 沖正銷帳
     * @param rs
     * @param payments
     * @throws SQLException
     */
    private void doEC1(ResultSet rs) throws SQLException
    {
        long uid = rs.getLong("UID");
        String virtualAccount = rs.getString("USERNO"); //虛擬帳號
        long txamt = Long.parseLong(rs.getString("TXAMT"));
        
        /* 沖正銷帳  沖正欄位為1 */ /* 已銷帳資料 */        
        List<Map<String, Object>> payments = this.bankingFeedbackDao.queryWriteOffByVirtualAccount(virtualAccount);
        
        if (0 == payments.size())
        {
            /* 未找到，需例外處理 */
            this.bankingFeedbackDao.updateWriteoffException(uid, "[沖正]無已銷帳資料");
        }
        else if (1 == payments.size())
        {
            Map<String, Object> payment = payments.get(0);
            
            Map<String, Object> writeoffPayment = this.bankingFeedbackDao.getWriteOffPayment((Number)payment.get("WRITE_OFF_PAYMENT_PK"));
            
            String channel = (String)writeoffPayment.get("CHANNEL");
            
            // 只有 0.臨櫃繳款  2.ATM或網路ATM  3.中心轉帳BIO  4.自行XML  8.跨行匯款   可沖正，其他通路不可沖正
            if ("0".equalsIgnoreCase(channel) || "2".equalsIgnoreCase(channel) || "3".equalsIgnoreCase(channel) ||
            	"4".equalsIgnoreCase(channel) || "8".equalsIgnoreCase(channel) )
            {
                /* 核對金額是否正確 */
                long amount = ((Number)writeoffPayment.get("AMOUNT")).longValue(); // 核銷金額
                if (txamt == amount )
                {
                    // Yes!
                    // 銷帳資料改為沖正銷帳 WRITE_OFF_PAYMENT.WRITE_OFF_STATUS = 5
                    this.bankingFeedbackDao.rollbackWriteoff((Number)payment.get("WRITE_OFF_PAYMENT_PK"));
                    
                    // 繳費資料改為待繳款 PAYMENT.PAYMENT_STATUS = 2, PAYMENT.WRITE_OFF_PAYMENT_PK = null, REAL_PAY_AMOUNT = null
                    this.bankingFeedbackDao.rollbackPayment((Number)payment.get("PAYMENT_PK"));
                    
                    this.bankingFeedbackDao.updateWriteoffDone(uid);
                }
                else
                {
                    /* 金額不符合，需例外處理 */
                    this.bankingFeedbackDao.updateWriteoffException(uid, "[沖正]金額不符合");
                }
                
            }
            else
            {
                /* 通路不符，需例外處理  */
                this.bankingFeedbackDao.updateWriteoffException(uid, "[沖正]通路不符");
            }                               
            
        }
        else
        {
            /* 多筆已消帳資料，需例外處理  */
            this.bankingFeedbackDao.updateWriteoffException(uid, "[沖正]多筆已消帳資料符合");
        }
        
    }
    
    @Transactional(readOnly = false)
    public void writeoffByFile2(List<Map<String, Object>> list) {
        /* 建立交易的定義，每一次都是新的 Transaction */
        DefaultTransactionDefinition definition = new DefaultTransactionDefinition(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
        try {
                for(int i=0;i<list.size();i++){                	
                	TransactionStatus status = this.transactionManager.getTransaction(definition);                    
                    Map<String, Object> map =(Map<String, Object>) list.get(i);
                    /* 每一筆都要作，就算中間有 exception */
                    try {
                    	processSingleWriteoffPayment2(map);
                        transactionManager.commit(status);
                    }
                    catch (Exception e) {
                        logger.error("[錯誤]"+ e.getMessage(),  e);
                    }
                }
                
        }catch (Exception ex){
            logger.error("[錯誤]" + ex.toString(), ex);         
        }
    }
    private void processSingleWriteoffPayment2(Map<String, Object> map)throws SQLException, ParseException {
    	
    	/* 繳款性質 --> 0 ：期付款、2：解約款 */
		if ("0".equals(map.get("PAY_TYPE").toString())) {
			logger.info("虛擬帳號:{}, 繳款期數:{}, 車貸正常銷帳", map.get("USERNO").toString() ,map.get("PERIOD").toString() );
			doEC2(map);			
		} else {
			logger.info("合約編號:{}, 繳款性質:{}, 車貸買回或提前還款", map.get("PAYER_ID").toString(), map.get("PAY_TYPE").toString());
			doEC3(map);			
		}
	}
    
    /**
     * 期數<>00，繳款帳號 + 金額正確，就進行銷帳(寫入銷帳檔)繳款狀態修改，異常寫Error Log(資料不存在)。
     * @param rs
     * @throws SQLException
     * @throws ParseException
     */
    private void doEC2(Map<String, Object> map) throws SQLException, ParseException  {
    	
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
        String virtualAccount = map.get("USERNO").toString(); //虛擬帳號   
        /* [0]UID [1]Channel */
        String rtn_Array[] = getUid(virtualAccount).split(",");
        long uid = Long.parseLong(rtn_Array[0]);
        long txamt = Long.parseLong(map.get("AMOUNT").toString());
        
        /* 正常銷帳  沖正欄位為0 */ /* 未銷帳資料 */                        
        List<Map<String, Object>> payments = this.bankingFeedbackDao.queryUnWriteOffByVirtualAccount(virtualAccount);
        
        if (0 == payments.size())
        {
        	logger.info("虛擬帳號無未銷帳資料:{}", virtualAccount );
        	/* 未找到，需例外處理 */
        	updateUid( virtualAccount, false, "無未銷帳資料");
        }
        else if (1 == payments.size())
        {
            Map<String, Object> payment = payments.get(0);
            /* 核對金額是否正確 */
            long totalAmount = ((Number)payment.get("TOTAL_AMOUNT")).longValue(); // 繳費總金額
            long totalLoanableAmount = 0;
            if (payment.get("TOTAL_LOANABLE_AMOUNT") != null){
                totalLoanableAmount = ((Number)payment.get("TOTAL_LOANABLE_AMOUNT")).longValue(); // 繳費總金額(可貸)
            }   
            if (txamt == totalAmount || txamt == totalLoanableAmount)
            {
                // Yes!
                Map<String, Object> writeoffPayment = createEmptyWriteoffPayment();
                
                /* 先新增到銷帳資料表*/

                writeoffPayment.put("PAYMENT_PK", payment.get("PAYMENT_PK"));
                writeoffPayment.put("AMOUNT", txamt);//銷帳金額  
                
                if (map.get("CHANNEL").toString().equals("0")){
                	/* 優利未回覆繳款管道 */
	                if (rtn_Array[1].length() != 1){
	                	/* 通路別CHANNEL ==>(D:統一超商, E:全家超商, F:萊爾富超商, G:OK超商, H:郵局 */
	                    writeoffPayment.put("CHANNEL", CHANEL_CODE.get(rtn_Array[1]));
	                }else{
	                	/* 通路別TRMTYP  ==>(0:臨櫃,1:網銀,2:ATM(或網路ATM),3:中心轉帳BIO),4:自行XML,8:跨行匯款 */
	                	writeoffPayment.put("CHANNEL", rtn_Array[1]);	
	                }
                }else{
                	/* 優利回覆繳款管道  1:7-11 2:萊爾富  3.全家  4:OK 5:郵局  */
                	writeoffPayment.put("CHANNEL", CHANEL_CODE.get(map.get("CHANNEL").toString()));	
                }
                writeoffPayment.put("TX_DATE", dateFormat.parseObject(map.get("TX_DATE").toString()));//實際繳款日 
                writeoffPayment.put("WRITE_OFF_DATE", new Date());//銷帳時間(繳款核銷日)                
                writeoffPayment.put("WRITE_OFF_STATUS", new Integer(1)); //銷帳狀態- 正常
                writeoffPayment.put("FEEDBACK_UID", new Long(uid));
                
                Number writeOffPaymentPk = this.bankingFeedbackDao.insertWriteOffPayment(writeoffPayment);
                
                /* 再更新 Payment */
                payment.put("REAL_PAY_AMOUNT", txamt);
                payment.put("WRITE_OFF_PAYMENT_PK", writeOffPaymentPk);
                payment.put("UPDATE_BY", "BankingFeedBackbackJob");
                payment.put("UPDATE_DATE", new Date());
                this.bankingFeedbackDao.updatePaymentToWriteOffStatus(payment);
                
                updateUid( virtualAccount, true, "");
                
            }
            else
            {
                /* 金額不符合，需例外處理 */
                updateUid( virtualAccount, false, "金額不符合");
                logger.error("虛擬帳號金額不符合:{},{}", virtualAccount, txamt);
            }
        }
        else
        {
            /* 只能有一筆未消帳資料，需例外處理  */ /* 源頭就有錯了，需要改轉入程式，或是修改規則 */
        	updateUid( virtualAccount, false, "多筆未銷帳資料符合");
        }
        
    }
    
    /**
     * 期數==00 ，繳款帳號取14碼，之後所有帳單皆取消，買回或提前清償Table，待回饋手續費使用。
     * @param rs
     * @throws SQLException
     */
    private void doEC3(Map<String, Object> map) throws SQLException
    {
    	/* 虛擬帳號 */       
    	String virtualAccount = map.get("USERNO").toString();        
        List<Map<String, Object>> payments = this.bankingFeedbackDao.queryWriteOffByVirtualAccount2(virtualAccount.substring(0, 14));
        
        if (0 == payments.size())
        {
            /* 未找到尚未買回或提前清償的帳單資料，需例外處理 */
        	logger.info("{}, 車貸無未銷帳資料", virtualAccount.substring(0, 14));        	
        } else {
        	String period = "";
        	Number group_pk = null;
        	for(int i=0;i<payments.size();i++){
        		Map<String, Object> payment = payments.get(i);
        		period = period + payment.get("VIRTUAL_ACCOUNT").toString().substring(14,16)+",";
        		group_pk = (Number)payment.get("GROUP_PK");
        		this.bankingFeedbackDao.rollbackPayment2((Number)payment.get("PAYMENT_PK"));
        		updateUid(payment.get("VIRTUAL_ACCOUNT").toString(), true, "車商已買回或提前清償");
        		/* 寫入帳單註銷檔 */
        		logger.info("註銷車商已買回或提前清償帳單:{}", payment.get("VIRTUAL_ACCOUNT").toString());
        		Map<String, Object> rd = new HashMap<String, Object>();
            	rd.put("PAYMENT_PK", (Number)payment.get("PAYMENT_PK"));
            	rd.put("CANCEL_DATE", new Date());
            	rd.put("CANCEL_USER_PK", 1);
            	rd.put("CANCEL_REASON", "車商已買回或提前清償");
            	rd.put("CONFIRM_STATULS", 1);
            	rd.put("CONFIRM_DATE", new Date());
            	rd.put("CONFIRM_USER_PK", 1);
            	this.bankingFeedbackDao.insertPayment_Canceled(rd);       
        	}        	 
        	/* 記錄這些帳單資料被車商買回提前清償 */
        	logger.info("記錄車商已買回或提前清償:{}", virtualAccount.substring(0,14));
        	Map<String, Object> rd = new HashMap<String, Object>();
        	rd.put("GROUP_PK", group_pk);
        	rd.put("FBFILENAME", map.get("sFileName"));
        	rd.put("VIRTUAL_ACCOUNT", virtualAccount);
        	rd.put("CONTRACT_NO", map.get("PAYER_ID"));
        	rd.put("CANCEL_TYPE", 1);
        	rd.put("CANCEL_DATE", new Date());
        	rd.put("CANCEL_TERM", period.substring(0,period.length()));
        	this.bankingFeedbackDao.insertCarloan_Cancel(rd);        	
        }
    }
    
    @Transactional(readOnly = false)
    public String getUid(String userno) {
        // 取得 DB Connection 
        Connection con = DataSourceUtils.getConnection(bankingFeedbackDao.getDataSource());
        Statement stmt = null;
        String UID ="0";
        String CHANNEL ="0";
        try {
            stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String sql = "select * from FEEDBACK_LOG where USERNO = '" + userno + "' AND FBCODE = '0' Order by UID Desc";
            ResultSet rs = null;
            try {
                rs = stmt.executeQuery(sql);
                if (rs.next()){
                	UID = String.valueOf(rs.getLong("UID")); 
                	CHANNEL = String.valueOf(rs.getString("CHANNEL"));
                }
            }
            finally {
                JdbcUtils.closeResultSet(rs);
            }
            return UID + "," + CHANNEL;
        }
        catch (Exception e) {
            JdbcUtils.closeStatement(stmt);
            stmt = null;
            DataSourceUtils.releaseConnection(con, bankingFeedbackDao.getDataSource());
            con = null;
            throw new RuntimeException(e);
        }
        finally
        {
            JdbcUtils.closeStatement(stmt);
            stmt = null;
            DataSourceUtils.releaseConnection(con, bankingFeedbackDao.getDataSource());
            con = null;
        }
    }
    
    public void updateUid(String userno,boolean bRtn,String ERR_MSG) {
        // 取得 DB Connection 
        Connection con = DataSourceUtils.getConnection(bankingFeedbackDao.getDataSource());
        Statement stmt = null;
        try {
            stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String sql = "select * from FEEDBACK_LOG where USERNO = '" + userno + "' AND FBCODE = '0' ";
            ResultSet rs = null;
            try {
                rs = stmt.executeQuery(sql);
                if (rs.next()){
                	do{
                	if (bRtn == true){
                		this.bankingFeedbackDao.updateWriteoffDone(rs.getLong("UID"));
                	}else{
                		this.bankingFeedbackDao.updateWriteoffException(rs.getLong("UID"), ERR_MSG);
                	}
                	}while (rs.next());
                }
            }
            finally {
                JdbcUtils.closeResultSet(rs);
            }
        }
        catch (Exception e) {
            JdbcUtils.closeStatement(stmt);
            stmt = null;
            DataSourceUtils.releaseConnection(con, bankingFeedbackDao.getDataSource());
            con = null;
            throw new RuntimeException(e);
        }
        finally
        {
            JdbcUtils.closeStatement(stmt);
            stmt = null;
            DataSourceUtils.releaseConnection(con, bankingFeedbackDao.getDataSource());
            con = null;
        }
    }
  
    public Map<String, Object> createEmptyWriteoffPayment()
    {
        Map<String, Object> rd = new HashMap<String, Object>();
        
        rd.put("PAYMENT_PK", null);
        rd.put("CHANNEL", null);
        rd.put("AMOUNT", null);
        rd.put("TX_DATE", null); //add by Jimmy Liu on 2011.04.07 因應報表需要 繳款交易日與繳款核銷日 修改
        rd.put("WRITE_OFF_DATE", null);
        rd.put("WRITE_OFF_STATUS", null);
        rd.put("FEEDBACK_UID", null);
        
        rd.put("CREATED_BY", "BankingFeedBackJob");
        rd.put("CREATED_DATE", new Date());
        rd.put("UPDATE_BY", "BankingFeedBackJob");
        rd.put("UPDATE_DATE", new Date());
        
        return rd;
    }

    /**
     * @param sFileName
     * @return
     */
    public boolean hasWriteoff(String fileName) {
        return this.bankingFeedbackDao.hasWriteoffLog(fileName);
    }

}
